/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package pesancharter.operator;

import Database.ConnectDB;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;

/**
 *
 * @author fendy
 */
public class PesanCharterEntity {

    int id_charter;
    int id_user;
    int id_mobil;
    Date date;
    int id_supir;
    int harga_charter;
    int status;
    int info_tambahan;
    String info_konfirm_bayar;

    @SuppressWarnings("static-access")
    public static void InsertPesanCharter(int id_user, String nama_pemesan, int id_mobil, Date date, int id_supir, int info_tambahan, String info_konfirm_bayar, int total_hari) throws SQLException{
        ConnectDB connDB = new ConnectDB("localhost:3306","PPL","cinta","cinta");
        // Search Harga Charter
        connDB.stmt = connDB.conn.createStatement();
        connDB.result = connDB.stmt.executeQuery("SELECT * FROM mobil_charter WHERE id_mobil = " + id_mobil);
        if (connDB.result.next()) {
            int id_jenis_mobil = connDB.result.getInt("id_jenis_mobil");
            connDB.stmt = connDB.conn.createStatement();
            connDB.result = connDB.stmt.executeQuery("SELECT * FROM jenis_mobil WHERE id_jenis_mobil = " + id_jenis_mobil);
            if (connDB.result.next()) {
                int harga_charter = connDB.result.getInt("tarif_hari");
                if (id_supir != -1)
                {
                    harga_charter += connDB.result.getInt("tarif_supir_hari");
                }
                for (int i=0; i<total_hari; i++)
                {
                    String query = "INSERT INTO pesanan_charter (id_user,nama_pemesan,id_mobil,tanggal_pinjam,id_supir,harga_charter,status,info_tambahan,info_konfirm_bayar) VALUES" +
                        "("+id_user+",'"+nama_pemesan+"',"+id_mobil+",'"+date+"',"+id_supir+","+harga_charter+",0,"+info_tambahan+",'"+info_konfirm_bayar+"')";
                    connDB.stmt.executeUpdate(query);
                    date = new Date(date.getTime()+86400000);
                }
            }
        } else { // Id mobil tidak ada
            System.err.print("ID Mobil tidak ditemukan");
        }
        connDB.Disconnect();
    }

    @SuppressWarnings("static-access")
    public static int GetAvailableSupir(Date date) throws SQLException
    {
        int id = -1;

        ConnectDB connDB = new ConnectDB("localhost:3306","PPL","cinta","cinta");
        connDB.stmt = connDB.conn.createStatement();
        connDB.result = connDB.stmt.executeQuery("SELECT * FROM sopir");
        
        ArrayList<Integer> idAllSupir = new ArrayList<Integer>();
        while (connDB.result.next())
        {
            idAllSupir.add(connDB.result.getInt("id_supir"));
        }

        for (int i=0; i<idAllSupir.size(); i++)
        {
            connDB.stmt = connDB.conn.createStatement();
            connDB.result = connDB.stmt.executeQuery("SELECT * FROM pesanan_charter where tanggal_pinjam='"+date+"' and id_supir="+idAllSupir.get(i));

            if (!connDB.result.next())
            {
                id = idAllSupir.get(i);
                break;
            }
        }
        ConnectDB.Disconnect();
        
        return id;
    }

    @SuppressWarnings("static-access")
    public static ArrayList<Integer> GetAllAvailableSupir(Date date) throws SQLException
    {
        ArrayList<Integer> idSupir = new ArrayList<Integer>();
        
        ConnectDB connDB = new ConnectDB("localhost:3306","PPL","cinta","cinta");
        connDB.stmt = connDB.conn.createStatement();
        connDB.result = connDB.stmt.executeQuery("SELECT * FROM sopir");

        ArrayList<Integer> idAllSupir = new ArrayList<Integer>();
        while (connDB.result.next())
        {
            idAllSupir.add(connDB.result.getInt("id_supir"));
        }
        
        for (int i=0; i<idAllSupir.size(); i++)
        {
            connDB.stmt = connDB.conn.createStatement();
            connDB.result = connDB.stmt.executeQuery("SELECT * FROM pesanan_charter where tanggal_pinjam='"+date+"' and id_supir="+idAllSupir.get(i));

            if (!connDB.result.next())
            {
                idSupir.add(idAllSupir.get(i));
            }
        }
        ConnectDB.Disconnect();

        return idSupir;
    }

    @SuppressWarnings("static-access")
    public static ArrayList<Integer> GetAvailableMobil(Date date) throws SQLException
    {
        ArrayList<Integer> idMobil = new ArrayList<Integer>();

        int id = -1;

        ConnectDB connDB = new ConnectDB("localhost:3306","PPL","cinta","cinta");
        connDB.stmt = connDB.conn.createStatement();
        connDB.result = connDB.stmt.executeQuery("SELECT * FROM mobil_charter");
        
        ArrayList<Integer> idAllMobil = new ArrayList<Integer>();
        while (connDB.result.next())
        {
            idAllMobil.add(connDB.result.getInt("id_mobil"));
        }

        for (int i=0; i<idAllMobil.size(); i++)
        {
            connDB.stmt = connDB.conn.createStatement();
            connDB.result = connDB.stmt.executeQuery("SELECT * FROM pesanan_charter where tanggal_pinjam='"+date+"' and id_mobil="+idAllMobil.get(i));

            if (!connDB.result.next())
            {
                idMobil.add(idAllMobil.get(i));
            }
        }
        ConnectDB.Disconnect();
        
        return idMobil;
    }

    @SuppressWarnings("static-access")
    public static ArrayList<String> GetJenisMobil(ArrayList<Integer> idMobil)
    {
        ArrayList<String> jenisMobil = new ArrayList<String>();
        
        try {
        ConnectDB connDB = new ConnectDB("localhost:3306","PPL","cinta","cinta");
        for (int i=0; i<idMobil.size(); i++)
        {
            connDB.stmt = connDB.conn.createStatement();
            connDB.result = connDB.stmt.executeQuery("SELECT * FROM mobil_charter, jenis_mobil where mobil_charter.id_jenis_mobil = jenis_mobil.id_jenis_mobil and mobil_charter.id_mobil='"+idMobil.get(i)+"'");
            if (connDB.result.next())
            {
                jenisMobil.add(connDB.result.getString("jenis_mobil"));
            }
        }
        }
        catch (SQLException e)
        {}

        return jenisMobil;
    }

    public static void main(String[] arg) {
        /*
        try {
            PesanCharterEntity.InsertPesanCharter(101, 1, new Date(110,10,01), 0, 0, null);
        } catch (SQLException ex) {
            Logger.getLogger(PesanCharterEntity.class.getName()).log(Level.SEVERE, null, ex);
        }
         * 
         */
    }

}
